﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Adams.Web.Data.Businesses
{
    public static class Index
    {
        public static Adams.Web.Models.Businesses.Index.PageLoad PageLoad()
        {
            var data = new Adams.Web.Models.Businesses.Index.PageLoad() { 
                Types =  new Models.Businesses.TypeCollection()
            };

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * 
FROM Businesses.Types 
ORDER BY Name

SELECT C.*, (
    SELECT COUNT(*) 
    FROM Businesses.Businesses P 
    WHERE P.CategoryID = C.CategoryID AND P.StatusID = 2 AND DATEDIFF(d, GETDATE(), P.ExpiresOn) >= 0) AS BusinessCount
FROM Businesses.Categories C 
ORDER BY C.Name
", CommandType.Text))
            {
                while (dr.Read())
                {
                    var type = DB.InitFromReader<Adams.Web.Models.Businesses.Type>(dr, new ColMap { NameInDb = "TypeID", NameInObject = "ID" });
                    type.Categories2 = new Adams.Web.Models.Businesses.CategoryCollection();
                    data.Types.Add(type);
                }
                dr.NextResult();
                while (dr.Read())
                {
                    var cat = DB.InitFromReader<Adams.Web.Models.Businesses.Category>(dr, new ColMap { NameInDb = "CategoryID", NameInObject = "ID" });
                    data.Types.Where(t => t.ID == dr["TypeID"].DbInt()).FirstOrDefault().Categories2.Add(cat);
                }
            }

            return data;
        }

    }
}